package com.example.demo.dao;

import com.example.demo.entity.Circle;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 圈子的数据库操作类
 */
public interface CircleDao extends JpaRepository<Circle,Integer> {

    @Query(value = "SELECT" +
            " c.*," +
            " u.nickname," +
            " u.avtar ," +
            " (select status from circle_like cl where cl.circleId = c.id and cl.userId = ?3) as status," +//这是新家的
            "(select GROUP_CONCAT(nickname) from user where id in  (select userId from circle_like where circleId = c.id and status =1)) AS likeUsers"+
            " FROM" +
            " circle c" +
            " JOIN user u ON c.userId = u.id " +
            " ORDER BY" +
            " time DESC " +
            " LIMIT ?1," +
            "?2",nativeQuery = true)
    List<Map<String,Object>> getList(int page, int pageSize,int userId);

    //获取指定用户ID发布的圈子列表
    @Query(value = "select c.*,u.nickname,u.avtar from circle c join user u on c.userId=u.id where c.userId=?3 order by time desc limit ?1,?2",nativeQuery = true)
    List<Map<String,Object>> myList(int page, int pageSize,int userId);

    List<Circle> findByUserId(int userId);

    @Query(value = "select c.*,u.nickname,u.avtar from circle c join user u on c.userId=u.id" +
    "where c.id in (select circleId from circle_like where userId = ?3) order by" +
    "time desc limit ?1,?2",nativeQuery = true)
    List<Map<String, Object>> myLikeList(int i, int pageSize, int id);

    @Query(value = "SELECT" +
    "C.*," +
    "u.nickname," +
    "u.avtar,"+
    "(select GROUP_CONCAT(nickname) from user where id in (select userId from circle_list where cicleId = c.id amd status =1))"+
    "FROM" +
    "circle c" +
    "JOIN user u ON c.userId = u.id" +
    "ORDER BV" +
    "time DESC" +
    "LIMIT ?1," +
    "?2",nativeQuery = true)
    List<Map<String, Object>> getAdminList(int i, int pageSize);
}
